﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Security.Cryptography;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using MDSCommon;
namespace MDSAdministration
{
    public class ExcelUploadAdvanced : ExcelUpload
    {

        private MainWindow form;
        public ExcelUploadAdvanced(MainWindow Form)
        {
            form = Form;
            StartProcess();
            SetTabItem(CurrentState);
        }
        public enum State
        {
            Load = 1,
            TableMapping = 2,
            DataMapping = 3,
            DataCheck = 4,
            Report = 5
        }

        public State CurrentState { get; set; }

        public void StartProcess()
        {
            CurrentState = State.Load;
        }

        public void SetTabItem(State state)
        {
            ((TabItem)(form.tbcExcelUploadAdv.Items[0])).IsEnabled = false;
            ((TabItem)(form.tbcExcelUploadAdv.Items[1])).IsEnabled = false;
            ((TabItem)(form.tbcExcelUploadAdv.Items[2])).IsEnabled = false;
            ((TabItem)(form.tbcExcelUploadAdv.Items[3])).IsEnabled = false;

            switch (CurrentState)
            {
                case State.Load:
                    form.btnExcelUploadAdvBack.IsEnabled = false;
                    form.tbcExcelUploadAdv.SelectedIndex = 0;
                    ((TabItem)(form.tbcExcelUploadAdv.Items[0])).IsEnabled = true;

                    form.btnExcelUploadAdvNext.Content = "Next";
                    break;
                case State.TableMapping:
                    form.tbcExcelUploadAdv.SelectedIndex = 1;
                    ((TabItem)(form.tbcExcelUploadAdv.Items[1])).IsEnabled = true;
                    form.btnExcelUploadAdvBack.IsEnabled = true;
                    break;
                case State.DataMapping:
                    form.tbcExcelUploadAdv.SelectedIndex = 2;
                    ((TabItem)(form.tbcExcelUploadAdv.Items[2])).IsEnabled = true;
                    break;
                case State.DataCheck:
                    DoWorkStateDataCheck();
                    form.tbcExcelUploadAdv.SelectedIndex = 3;
                    ((TabItem)(form.tbcExcelUploadAdv.Items[3])).IsEnabled = true;
                    break;
                case State.Report:
                    DoWorkStateReport();
                    form.tbcExcelUploadAdv.SelectedIndex = 4;
                    ((TabItem)(form.tbcExcelUploadAdv.Items[4])).IsEnabled = true;
                    form.btnExcelUploadAdvBack.IsEnabled = false;
                    form.btnExcelUploadAdvNext.Content = "Finished";
                    break;
            }
        }

        private void DoWorkStateDataCheck()
        {
            form.lbxExcelUploadAdvancedDataCheck.ItemsSource = null;
            form.dgExcelUploadAdvDataCheck.ItemsSource = null;
        }
        private void DoWorkStateReport()
        {
            form.lbxExcelUploadAdvancedResults.ItemsSource = null;
            form.dgExcelUploadAdvReportData.ItemsSource = null;
            GetReportData();
        }
        public void NextState()
        {
            bool gotoNextState = true;
            switch (CurrentState)
            {
                case State.Load:
                    if (CheckLoad())
                    {
                        GetLoadData();
                        CurrentState = State.TableMapping;
                    }
                    else
                    {
                        gotoNextState = false;
                    }
                     
                    break;
                case State.TableMapping:
                    if (CheckSchema())
                    {
                        LoadDataIntoRawTables();
                        LoadDataRegions();
                        CurrentState = State.DataMapping;
                    }
                    else
                    {
                        gotoNextState = false;
                    }
                    break;

                case State.DataMapping:
                    CurrentState = State.DataCheck;
                    break;
                case State.DataCheck:                    
                    CurrentState = State.Report;
                    break;
                case State.Report:
                    CurrentState = State.Load;
                    break;
            }

            if (gotoNextState)
            {
                SetTabItem(CurrentState);
            }
        }

        private void LoadDataRegions()
        {
            if (!form.cbxExcelUploadAdvDataCheckRegion.HasItems)
            {
                DataSet dsRegion = DataHelper.GetData("EXEC [dbo].[ExtractTableLoadGetRegions]",
                    form.getServer().ServerName, form.txtExcelUploadAdvDatabaseName.Text);
                if (dsRegion.Tables.Count > 0)
                {
                    form.cbxExcelUploadAdvDataCheckRegion.ItemsSource= dsRegion.Tables[0].DefaultView;
                    form.cbxExcelUploadAdvDataCheckRegion.DisplayMemberPath = "RegionCode";
                    form.cbxExcelUploadAdvDataCheckRegion.SelectedValuePath = "RegionCode";
                }
            }
        }

        private DataSet ds;
        private void GetReportData()
        {
            
            ds = DataHelper.GetData("EXEC [dbo].[ExtractReportData" + form.getServer().Model + "]", form.getServer().ServerName, form.txtExcelUploadAdvDatabaseName.Text);

            for (int index = 0; index<ds.Tables[0].Rows.Count; index++)
            {
                ds.Tables[index + 1].TableName = ds.Tables[0].Rows[index]["TableName"].ToString();
            }

            ds.Tables.RemoveAt(0);

            form.lbxExcelUploadAdvancedResults.ItemsSource = ds.Tables;
            form.lbxExcelUploadAdvancedResults.DisplayMemberPath = "TableName";
        }
        public void BackState()
        {
            switch (CurrentState)
            {
                case State.Load:
                    return;                    
                case State.TableMapping:
                    CurrentState = State.Load;
                    break;
                case State.DataMapping:
                    CurrentState = State.TableMapping;
                    break;
                case State.DataCheck:
                    CurrentState = State.DataMapping;
                    break;
                case State.Report:
                    return;
            }
            SetTabItem(CurrentState);
        }

        private void LoadDataIntoRawTables()
        {
            using (new WaitCursor())
            {
                foreach (var sheet in Sheets)
                {
                    if (sheet.IsValid == false && sheet.Load == true)
                    {
                        MessageBox.Show("The excel sheet has errors: " + Environment.NewLine +
                                        sheet.Message());
                        return;
                    }
                }

                if (form.cbxExcelUploadAdvancedDeleteRaw.IsChecked.HasValue && form.cbxExcelUploadAdvancedDeleteRaw.IsChecked.Value)
                {
                    string sqlRawDataDelete = "EXEC dbo.RawTablesTruncate";
                    DataHelper.RunQuery(sqlRawDataDelete, form.getServer().ServerName,
                        form.txtExcelUploadAdvDatabaseName.Text);
                }

                if (form.cbxExcelUploadAdvancedDeleteBKMappig.IsChecked.HasValue && form.cbxExcelUploadAdvancedDeleteBKMappig.IsChecked.Value)
                {
                    string sqlDelete = "DELETE FROM dbo.BKToMdmMapping";
                    DataHelper.RunQuery(sqlDelete, form.getServer().ServerName, form.txtExcelUploadAdvDatabaseName.Text);

                }

                string sqlUpdateTableMappings = "UPDATE dbo.TableMappings SET LoadIntoMDM = 0";
                DataHelper.RunQuery(sqlUpdateTableMappings, form.getServer().ServerName,
                    form.txtExcelUploadAdvDatabaseName.Text);

                foreach (var sheet in Sheets.Where(s => s.Load == true))
                {
                    SqlConnectionStringBuilder scb = new SqlConnectionStringBuilder();
                    scb.DataSource = form.getServer().ServerName;
                    scb.InitialCatalog = form.txtExcelUploadAdvDatabaseName.Text;
                    scb.IntegratedSecurity = true;
                    scb.ConnectTimeout = 0;

                    using (SqlConnection sqlConn = new SqlConnection(scb.ConnectionString))
                    {
                        try
                        {
                            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn))
                            {
                                bulkCopy.DestinationTableName = form.txtExcelUploadAdvSchema.Text + "." +
                                                                form.getServer().Model + "_" + sheet.Name;

                                foreach (DataColumn col in sheet.Data.Columns)
                                {
                                    if (!col.ColumnName.Contains(Constants.EXCELUPLOAD_MDSADMIN))
                                    {
                                        bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(col.ColumnName,
                                            col.ColumnName));
                                    }
                                }

                                bulkCopy.BatchSize = 1000;
                                // Write from the source to the destination.
                                sqlConn.Open();
                                bulkCopy.WriteToServer(sheet.Data);

                                sqlUpdateTableMappings =
                                    "UPDATE dbo.TableMappings SET LoadIntoMDM = 1 WHERE ExtractTableName = '" +
                                    form.getServer().Model + "_" + sheet.Name + "'";

                                DataHelper.RunQuery(sqlUpdateTableMappings, form.getServer().ServerName,
                                    form.txtExcelUploadAdvDatabaseName.Text);
                            }
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine(ex.Message);
                        }
                    }
                }
            }
        }

        private bool CheckSchema()
        {
            var dv = ((DataView)form.dgExcelUploadAdvTableMappings.ItemsSource);

            var query = from r in dv.Table.AsEnumerable()
                        where r.Field<int>("RawTableExists") == 0 ||
                            r.Field<int>("RawColumnsMatch") == 0
                        select r;

            if (query.Count() > 0)
            {
                MessageBox.Show("The excel file format and raw table format do not match.");
                return false;
            }

            return true;
        }

        private void GetLoadData()
        {
            var model = form.getServerDirectionModel();
            var server = form.getServer();
            //TODO
            //string updatequery = "UPDATE " + form.txtExcelUploadAdvDatabaseName.Text + ".dbo.TableMappings SET LoadIntoMDM =0";

            string ColsQuery = "";
            var Sheetsfiltered = Sheets.Where(s => s.Load == true);
            foreach (var sheet in Sheetsfiltered)
            {
                ColsQuery = BuildTableQuery(sheet, model, ColsQuery);
            }

            DataSet dsCheckDB = DataHelper.CheckDatabaseExists(server.ServerName, form.txtExcelUploadAdvDatabaseName.Text);
            if (dsCheckDB.Tables.Count > 0 && dsCheckDB.Tables[0].Rows.Count == 0)
            {
                MessageBox.Show("The database " + form.txtExcelUploadAdvDatabaseName.Text + " can not be found.");
                return;
            }

string query = "IF OBJECT_ID('tempdb..#Excel') IS NOT NULL DROP TABLE #Excel" + Environment.NewLine;
query += "SELECT * INTO #Excel FROM" + Environment.NewLine;
query += "(" + Environment.NewLine;
query += ColsQuery;
query += ")excel" + Environment.NewLine;
query += Environment.NewLine;
query += "SELECT ExcelTableName, ID,ExtractTableName,MDMEntityName,MDMModelName, ParentForeignID, AutoGenerate,CustomSequence,LoadOrder,LoadIntoMDM,MAX(RawTableExists) AS 'RawTableExists'," + Environment.NewLine;
query += "MIN(RawColumnExists) AS 'RawColumnsMatch'" + Environment.NewLine;
query += "FROM" + Environment.NewLine;
query += "(" + Environment.NewLine;
query += "SELECT COALESCE(excel.TableName, c.TableName) AS ExcelTableName, " + Environment.NewLine;
query += "COALESCE(tm.ID, c.ID) AS ID," + Environment.NewLine;
query += "COALESCE(tm.ExtractTableName, c.ExtractTableName) AS ExtractTableName," + Environment.NewLine;
query += "COALESCE(tm.MDMEntityName, c.MDMEntityName) AS MDMEntityName, " + Environment.NewLine;
query += "COALESCE(tm.MDMModelName, c.MDMModelName) AS MDMModelName," + Environment.NewLine;
query += "COALESCE( tm.ParentForeignID, c.ParentForeignID) AS ParentForeignID," + Environment.NewLine;
query += "COALESCE(tm.AutoGenerate, c.AutoGenerate) AS AutoGenerate," + Environment.NewLine;
query += "COALESCE(tm.CustomSequence, c.CustomSequence) AS CustomSequence," + Environment.NewLine;
query += "COALESCE(tm.LoadOrder, c.LoadOrder) AS LoadOrder," + Environment.NewLine;
query += "CAST(1 AS BIT) AS 'LoadIntoMDM'," + Environment.NewLine;
query += "CASE WHEN c.TABLE_NAME IS NULL  THEN 0 ELSE 1 END AS 'RawTableExists'," + Environment.NewLine;
query += "CASE WHEN c.COLUMN_NAME IS NULL OR excel.ColumnName IS NULL THEN 0 ELSE 1 END AS 'RawColumnExists'" + Environment.NewLine;
query += "FROM #Excel excel" + Environment.NewLine;
query += "LEFT JOIN MDMGlobalLoad.dbo.TableMappings tm ON tm.ExtractTableName = excel.TableName" + Environment.NewLine;
query += "FULL JOIN (SELECT excel.TableName, tm.ID,tm.ExtractTableName,MDMEntityName,tm.MDMModelName, tm.ParentForeignID, tm.AutoGenerate,tm.CustomSequence,tm.LoadOrder, c.TABLE_NAME, c.COLUMN_NAME FROM MDMGlobalLoad.INFORMATION_SCHEMA.COLUMNS c " + Environment.NewLine;
query += "INNER JOIN #Excel excel ON c.TABLE_SCHEMA = 'raw' AND excel.TableName = c.TABLE_NAME" + Environment.NewLine;
query += "LEFT JOIN MDMGlobalLoad.dbo.TableMappings tm ON tm.ExtractTableName = excel.TableName) c ON excel.TableName = c.TABLE_NAME AND excel.ColumnName = c.COLUMN_NAME" + Environment.NewLine;
query += " ) a" + Environment.NewLine;
query += "GROUP BY ExcelTableName,ID,ExtractTableName,MDMEntityName,MDMModelName, ParentForeignID, AutoGenerate, CustomSequence, LoadOrder, LoadIntoMDM" + Environment.NewLine;
query += "IF OBJECT_ID('tempdb..#Excel') IS NOT NULL DROP TABLE #Excel" + Environment.NewLine;

            var result = DataHelper.GetData(query, server);

            if (result != null && result.Tables.Count > 0)
            {
                form.dgExcelUploadAdvTableMappings.ItemsSource = result.Tables[0].DefaultView;
                form.dgExcelUploadAdvTableMappingsDifCol.ItemsSource = null;
            }

        }

        private string BuildTableQuery(SheetDetailLoad sheet, CompareModel model, string ColsQuery)
        {
            if (sheet.Data != null)
            {
                foreach (DataColumn col in sheet.Data.Columns)
                {
                    if (col.ColumnName.Contains(Constants.EXCELUPLOAD_MDSADMIN))
                    {
                        continue;
                    }

                    if (ColsQuery.Length > 0)
                    {
                        ColsQuery += "UNION" + Environment.NewLine;
                    }
                    ColsQuery += "SELECT '" + model.Name + "_" + sheet.Name + "' AS 'TableName', '" + col.ColumnName +
                                 "' AS 'ColumnName'" + Environment.NewLine;
                }
            }
            else
            {
                throw new Exception("Sheet Data is empty.");
            }
            return ColsQuery;
        }

        private bool CheckLoad()
        {
            if (Sheets.Count == 0)
            {
                MessageBox.Show("No Excel Sheets loaded");
                return false;
            }

            bool isLoad = false;

            foreach (var sdl in Sheets)
            {
                if (sdl.Load == true)
                {
                    isLoad = true;

                    if (sdl.Count == 0)
                    {
                        MessageBox.Show("Excel Sheet: " + sdl.Name + " has zero count. The sheet can not be loaded.");
                        return false;
                    }
                }
            }

            if (isLoad == false)
            {
                MessageBox.Show("No Excel Sheets has been selected to be loaded");
                return false;
            }
            return true;
        }

        

        public void GetTableDetails(string TableName)
        {

            string name = TableName.Split('_')[1];
            var sheet = Sheets.Find(s => s.Name == name);


            var model = form.getServerDirectionModel();
            var server = form.getServer();
            string ColsQuery = "";

            ColsQuery = BuildTableQuery(sheet, model, ColsQuery);


            string query = "SELECT excel.ColumnName AS 'Excel', c.COLUMN_NAME AS 'Table'" + Environment.NewLine;
            query += "FROM (" + Environment.NewLine;
            query += ColsQuery;
            query += ")excel" + Environment.NewLine;
            query += "FULL JOIN" + Environment.NewLine;
            query += "(SELECT c.TABLE_NAME, c.COLUMN_NAME FROM MDMGlobalLoad.INFORMATION_SCHEMA.COLUMNS c WHERE c.TABLE_NAME = '" + model.Name + "_" + sheet.Name + "' AND c.TABLE_SCHEMA = 'raw') c ON excel.ColumnName = c.COLUMN_NAME" + Environment.NewLine;

            var result = DataHelper.GetData(query, server);

            if (result != null && result.Tables.Count > 0)
            {
                form.dgExcelUploadAdvTableMappingsDifCol.ItemsSource = result.Tables[0].DefaultView;
            }
        }

        public void CreateSqlTable(string TableName)
        {
            string name = TableName.Split('_')[1];
            var sheet = Sheets.Find(s => s.Name == name);

            string columns = "";
            foreach (DataColumn col in sheet.Data.Columns)
            {
                if (!col.ColumnName.Contains(Constants.EXCELUPLOAD_MDSADMIN))
                {
                    if (columns.Length > 0)
                    {
                        columns += "," + Environment.NewLine;
                    }
                    columns += "[" + col.ColumnName + "] [nvarchar](1000) NULL";
                }
            }

            string query = "IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '" + form.txtExcelUploadAdvSchema.Text + "' AND  TABLE_NAME = '" + TableName + "'))" + Environment.NewLine;
            query += "BEGIN" + Environment.NewLine;
            query += "   DROP TABLE [" + form.txtExcelUploadAdvSchema.Text + "].[" + TableName + "]" + Environment.NewLine;
            query += "END" + Environment.NewLine;
            query += "" + Environment.NewLine;
            query += "CREATE TABLE [" + form.txtExcelUploadAdvSchema.Text + "].[" + TableName + "](" + Environment.NewLine;
            query += columns;
            query += ") ON [PRIMARY]" + Environment.NewLine;
            query += "GO" + Environment.NewLine;

            var d = new DeploymentWindow();
            var db = new DeploymentBuilder();
            db.sqlStatement = query;
            var server = form.getServer();

            db.Server = new ServerMDSDetails()
            {
                Database = form.txtExcelUploadAdvDatabaseName.Text,
                Model = server.Model,
                ModelVersion = server.ModelVersion,
                ServerName = server.ServerName
            };
            d.db = db;
            d.ShowDialog();
        }


        internal void Refresh()
        {
            switch (CurrentState)
            {
                case State.Load:
                    Sheets.Clear();
                    GetExcelSheetNames();
                    form.LoadDdgExcelUploadAdvLoadFiles(Sheets);
                    form.dgExcelUploadAdvLoadFileData.ItemsSource = null;
                    break;
                case State.TableMapping:
                    GetLoadData();
                    break;
                case State.DataCheck:
                    break;
                case State.Report:
                    GetReportData();
                    break;
            }
            
            
        }

        internal void LoadFileHeadCheck(object sender, RoutedEventArgs e, bool p)
        {
            //TODO fix this logic to select all files
            //foreach (SheetDetailLoad sheet in Sheets)
            //{
            //    if (sheet.Data == null)
            //    {
            //        LoadSpreadSheet(sheet, true, FileName,)
            //    }
            //}
            //Sheets
            //if (Sheet.Data == null)
            //{
            //    try
            //    {
            //        using (new WaitCursor())
            //        {
            //            var result = eua.LoadSpreadSheet(Sheet, true, txtExcelUploadAdvFile.Text,
            //                getServerDirectionModel(sDirection));
            //            if (result == null)
            //            {
            //                dgExcelUploadAdvLoadFileData.ItemsSource = null;
            //                if (!Sheet.IsValid)
            //                {
            //                    MessageBox.Show(Sheet.Message(), "Spreadsheet Tab Error", MessageBoxButton.OK,
            //                        MessageBoxImage.Error);
            //                }
            //            }
            //            else
            //            {
            //                dgExcelUploadAdvLoadFileData.ItemsSource = result;
            //            }

            //        }
            //    }
            //    catch (SystemException ex)
            //    {
            //        MessageBox.Show("Error has occured while loading the excel file", "Error", MessageBoxButton.OK,
            //            MessageBoxImage.Error);
            //        Console.WriteLine("Message: " + Environment.NewLine + ex.Message);
            //        Console.WriteLine("InnerException: " + Environment.NewLine + ex.InnerException);
            //        Console.WriteLine("StackTrace: " + Environment.NewLine + ex.StackTrace);
            //    }
            //}
            //else
            //{
            //    dgExcelUploadAdvLoadFileData.ItemsSource = Sheet.Data.DefaultView;
            //}


        }
    }
}
